﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StockCommon.baseclass;
using Utilities;
using StockCommon.Calendar;

namespace StockCommon.data
{
    public class StockDailyIndexServices:DBAccess
    {
        public static int getMaxTradeCount(String code,int days)
        {
            String pat = "select max(c) from (select top {1} 成交筆數 as c from StockDaily where  證券代號='{0}' order by StockDaily.日期 desc) T";
            String sql=String.Format(pat, code, days);
            var c=getContext().ExecuteQuery<int?>(sql).FirstOrDefault();
            if (c != null)
                return Convert.ToInt32(c);
            return -1;
        }
        public static int getTradeCount(String code,DateTime date)
        {
            String pat = @"select 成交筆數 from StockDaily where 
                                       證券代號='{0}' and StockDaily.日期='{1}'";
            String sql=String.Format(pat, code, DateUtils.toShortDateFormat(date));
            var ii=getContext().ExecuteQuery<int?>(sql).FirstOrDefault();
            if (ii != null)
            {
                return Convert.ToInt32(ii);
            }
            return -1;
        }

        public static HashSet<String> getLastValidCodes(DateTime from,TwnStockMarketType m)
        {
            DateTime to = from;
            while (true)
            {
                HashSet<String> result = getValidCodes(from, to, m);
                if (result.Count > 0)
                    return result;
                from = from.AddDays(-1);
            }
        }
        public static HashSet<String> getValidCodes(DateTime from, DateTime to, TwnStockMarketType m)
        {
            String sql=String.Format("select distinct StockDaily.證券代號  from StockDaily where StockDaily.日期>='{0}' and  StockDaily.日期<='{1}'",DateUtils.toShortDateFormat(from),DateUtils.toShortDateFormat(to));
            List<String> ll=getContext().ExecuteQuery<String>(sql).ToList<String>();
            if (ll.Count == 0)
                return  new HashSet<String>();
            return StockCodeManager.filterForMarket(ll,m);
        }

        public static DateTime getLastTradeDate()
        {
            DateTime dt = DateTime.Now;
            if (dt.CompareTo(DateUtils.get1330Clock(dt))<0)
            {
                dt=dt.AddDays(-1);
            }
            dt = DateUtils.getDateOnly(dt);
            while (true)
            {
                if (CalenderHelper.isWorkDay(dt))
                    return dt;
                dt = dt.AddDays(-1);
            }
        }
        public static List<DateTime> getTradeDays(DateTime from, int count)
        {
            List<DateTime> result = new List<DateTime>();
            DateTime dt = DateUtils.getDateOnly(from);
            while (true)
            {
                if (CalenderHelper.isWorkDay(dt))
                    result.Add(dt);
                dt = dt.AddDays(-1);
                if (result.Count >= count)
                    return result;
            }
        }
        protected static String getMarket(TwnStockMarketType market)
        {
            if (market == TwnStockMarketType.上市)
                return "tse";
            if (market == TwnStockMarketType.上櫃)
                return "otc";
            return "N/A";

        }
        public static List<String> getTradeStockCode(DateTime date, TwnStockMarketType market)
        {
            String pat = @"select StockIndex.代號 from StockIndex where  StockIndex.市場='{0}' and StockIndex.代號 in 
                                     (Select distinct StockDaily.證券代號  from StockDaily where StockDaily.日期='{1}') ";
            String sql=String.Format(pat, EnumUtils.GetStringValue(market), DateUtils.toShortDateFormat(date));
            List<String> result=getContext().ExecuteQuery<String>(sql).ToList();
            return result;
        }

        public static int getRecordCount(DateTime date, TwnStockMarketType market)
        {
            String m= getMarket(market);
            String ds=DateUtils.toShortDateFormat_NoSlash(date);
            String sql=String.Format("select count(代號) as c from StockIndex where StockIndex.市場='{0}' and 代號 in (select distinct 證券代號  from StockDaily where StockDaily.日期='{1}')", m, ds);

            int c = getContext().ExecuteQuery<int>(sql).First();
            return c;
        }

        public static int deleteRecord(DateTime date, TwnStockMarketType market)
        {
            //TODO deleteRecord
            String m = getMarket(market);
            String ds = DateUtils.toShortDateFormat_NoSlash(date);
            String sql = String.Format("delete from stockdaily where 日期='{0}' and 證券代號 in (select 代號 from StockIndex where StockIndex.市場='{1}')", ds, m);
            int c = getContext().ExecuteCommand(sql);
            return c;
        }
    }


}
